﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Matrix.Common.DBUtility;
using Matrix.MaKaYunShang.Model;
using Matrix.MaKaYunShang.Model.Dto;

namespace Matrix.MaKaYunShang.DAL
{
    public partial class OrdersDAL : BaseDAL<OrdersDAL>
    {
        #region 默认方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Orders model)
        {
            var strSql = new StringBuilder();
            strSql.Append("insert into Orders(");
            strSql.Append("OrderNo,OrderTime,OrderStatus,OrderTitle,ShopId,ShopCustomerNumber,OldOrderPhone,CommodityQuantity,PayPrice,DiscountPrice,NormalPrice,RandomPayDiscount,CouponId,CouponValue,PayWay,PaySerialId,ReceiverName,ReceiverPhone,ReceiverAddress,Remark,SysRemark,IsPayCompleted,ConsumerId,ConsumerName,AssignerAccountId,AssignTime,DeliveryAccountId,DeliveryTime,ReceivingTime,FinishTime,CancelTime,RejectTime,RemindTime,ConsumerSendTime,CheckCode,IsPlatformOrder,IsEndingAfterPaid,ShareToken,Longitude,Latitude,IpAddress,OrderIdCopiedFrom,IsConvertFromOldVirtual,PriceDiffOfCopiedOrder,ConsumerOrderType,IsCommented,CanWithdraw,IsShopCanView,IsDelete");
            strSql.Append(") values (");
            strSql.Append("@OrderNo,@OrderTime,@OrderStatus,@OrderTitle,@ShopId,@ShopCustomerNumber,@OldOrderPhone,@CommodityQuantity,@PayPrice,@DiscountPrice,@NormalPrice,@RandomPayDiscount,@CouponId,@CouponValue,@PayWay,@PaySerialId,@ReceiverName,@ReceiverPhone,@ReceiverAddress,@Remark,@SysRemark,@IsPayCompleted,@ConsumerId,@ConsumerName,@AssignerAccountId,@AssignTime,@DeliveryAccountId,@DeliveryTime,@ReceivingTime,@FinishTime,@CancelTime,@RejectTime,@RemindTime,@ConsumerSendTime,@CheckCode,@IsPlatformOrder,@IsEndingAfterPaid,@ShareToken,@Longitude,@Latitude,@IpAddress,@OrderIdCopiedFrom,@IsConvertFromOldVirtual,@PriceDiffOfCopiedOrder,@ConsumerOrderType,@IsCommented,@CanWithdraw,@IsShopCanView,@IsDelete");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@OrderNo", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@OrderTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@OrderStatus", SqlDbType.Int,4) ,            
                        new SqlParameter("@OrderTitle", SqlDbType.NVarChar,255) ,            
                        new SqlParameter("@ShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ShopCustomerNumber", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@OldOrderPhone", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@CommodityQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@PayPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@DiscountPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@NormalPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@RandomPayDiscount", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@CouponId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CouponValue", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@PayWay", SqlDbType.Int,4) ,            
                        new SqlParameter("@PaySerialId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ReceiverName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@ReceiverPhone", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@ReceiverAddress", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Remark", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@SysRemark", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@IsPayCompleted", SqlDbType.Bit,1) ,            
                        new SqlParameter("@ConsumerId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ConsumerName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@AssignerAccountId", SqlDbType.Int,4) ,            
                        new SqlParameter("@AssignTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@DeliveryAccountId", SqlDbType.Int,4) ,            
                        new SqlParameter("@DeliveryTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@ReceivingTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@FinishTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@CancelTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@RejectTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@RemindTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@ConsumerSendTime", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@CheckCode", SqlDbType.NVarChar,10) ,            
                        new SqlParameter("@IsPlatformOrder", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsEndingAfterPaid", SqlDbType.Bit,1) ,            
                        new SqlParameter("@ShareToken", SqlDbType.NVarChar,150) ,            
                        new SqlParameter("@Longitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@Latitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@IpAddress", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@OrderIdCopiedFrom", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsConvertFromOldVirtual", SqlDbType.Bit,1) ,            
                        new SqlParameter("@PriceDiffOfCopiedOrder", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@ConsumerOrderType", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsCommented", SqlDbType.Bit,1) ,            
                        new SqlParameter("@CanWithdraw", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsShopCanView", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.OrderNo;
            parameters[1].Value = model.OrderTime;
            parameters[2].Value = model.OrderStatus;
            parameters[3].Value = model.OrderTitle;
            parameters[4].Value = model.ShopId;
            parameters[5].Value = model.ShopCustomerNumber;
            parameters[6].Value = model.OldOrderPhone;
            parameters[7].Value = model.CommodityQuantity;
            parameters[8].Value = model.PayPrice;
            parameters[9].Value = model.DiscountPrice;
            parameters[10].Value = model.NormalPrice;
            parameters[11].Value = model.RandomPayDiscount;
            parameters[12].Value = model.CouponId;
            parameters[13].Value = model.CouponValue;
            parameters[14].Value = model.PayWay;
            parameters[15].Value = model.PaySerialId;
            parameters[16].Value = model.ReceiverName;
            parameters[17].Value = model.ReceiverPhone;
            parameters[18].Value = model.ReceiverAddress;
            parameters[19].Value = model.Remark;
            parameters[20].Value = model.SysRemark;
            parameters[21].Value = model.IsPayCompleted;
            parameters[22].Value = model.ConsumerId;
            parameters[23].Value = model.ConsumerName;
            parameters[24].Value = model.AssignerAccountId;
            parameters[25].Value = model.AssignTime;
            parameters[26].Value = model.DeliveryAccountId;
            parameters[27].Value = model.DeliveryTime;
            parameters[28].Value = model.ReceivingTime;
            parameters[29].Value = model.FinishTime;
            parameters[30].Value = model.CancelTime;
            parameters[31].Value = model.RejectTime;
            parameters[32].Value = model.RemindTime;
            parameters[33].Value = model.ConsumerSendTime;
            parameters[34].Value = model.CheckCode;
            parameters[35].Value = model.IsPlatformOrder;
            parameters[36].Value = model.IsEndingAfterPaid;
            parameters[37].Value = model.ShareToken;
            parameters[38].Value = model.Longitude;
            parameters[39].Value = model.Latitude;
            parameters[40].Value = model.IpAddress;
            parameters[41].Value = model.OrderIdCopiedFrom;
            parameters[42].Value = model.IsConvertFromOldVirtual;
            parameters[43].Value = model.PriceDiffOfCopiedOrder;
            parameters[44].Value = model.ConsumerOrderType;
            parameters[45].Value = model.IsCommented;
            parameters[46].Value = model.CanWithdraw;
            parameters[47].Value = model.IsShopCanView;
            parameters[48].Value = model.IsDelete; var obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Orders model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update Orders set ");

            strSql.Append(" OrderNo = @OrderNo , ");
            strSql.Append(" OrderTime = @OrderTime , ");
            strSql.Append(" OrderStatus = @OrderStatus , ");
            strSql.Append(" OrderTitle = @OrderTitle , ");
            strSql.Append(" ShopId = @ShopId , ");
            strSql.Append(" ShopCustomerNumber = @ShopCustomerNumber , ");
            strSql.Append(" OldOrderPhone = @OldOrderPhone , ");
            strSql.Append(" CommodityQuantity = @CommodityQuantity , ");
            strSql.Append(" PayPrice = @PayPrice , ");
            strSql.Append(" DiscountPrice = @DiscountPrice , ");
            strSql.Append(" NormalPrice = @NormalPrice , ");
            strSql.Append(" RandomPayDiscount = @RandomPayDiscount , ");
            strSql.Append(" CouponId = @CouponId , ");
            strSql.Append(" CouponValue = @CouponValue , ");
            strSql.Append(" PayWay = @PayWay , ");
            strSql.Append(" PaySerialId = @PaySerialId , ");
            strSql.Append(" ReceiverName = @ReceiverName , ");
            strSql.Append(" ReceiverPhone = @ReceiverPhone , ");
            strSql.Append(" ReceiverAddress = @ReceiverAddress , ");
            strSql.Append(" Remark = @Remark , ");
            strSql.Append(" SysRemark = @SysRemark , ");
            strSql.Append(" IsPayCompleted = @IsPayCompleted , ");
            strSql.Append(" ConsumerId = @ConsumerId , ");
            strSql.Append(" ConsumerName = @ConsumerName , ");
            strSql.Append(" AssignerAccountId = @AssignerAccountId , ");
            strSql.Append(" AssignTime = @AssignTime , ");
            strSql.Append(" DeliveryAccountId = @DeliveryAccountId , ");
            strSql.Append(" DeliveryTime = @DeliveryTime , ");
            strSql.Append(" ReceivingTime = @ReceivingTime , ");
            strSql.Append(" FinishTime = @FinishTime , ");
            strSql.Append(" CancelTime = @CancelTime , ");
            strSql.Append(" RejectTime = @RejectTime , ");
            strSql.Append(" RemindTime = @RemindTime , ");
            strSql.Append(" ConsumerSendTime = @ConsumerSendTime , ");
            strSql.Append(" CheckCode = @CheckCode , ");
            strSql.Append(" IsPlatformOrder = @IsPlatformOrder , ");
            strSql.Append(" IsEndingAfterPaid = @IsEndingAfterPaid , ");
            strSql.Append(" ShareToken = @ShareToken , ");
            strSql.Append(" Longitude = @Longitude , ");
            strSql.Append(" Latitude = @Latitude , ");
            strSql.Append(" IpAddress = @IpAddress , ");
            strSql.Append(" OrderIdCopiedFrom = @OrderIdCopiedFrom , ");
            strSql.Append(" IsConvertFromOldVirtual = @IsConvertFromOldVirtual , ");
            strSql.Append(" PriceDiffOfCopiedOrder = @PriceDiffOfCopiedOrder , ");
            strSql.Append(" ConsumerOrderType = @ConsumerOrderType , ");
            strSql.Append(" IsCommented = @IsCommented , ");
            strSql.Append(" CanWithdraw = @CanWithdraw , ");
            strSql.Append(" IsShopCanView = @IsShopCanView , ");
            strSql.Append(" IsDelete = @IsDelete  ");
            strSql.Append(" where Id=@Id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@Id", SqlDbType.Int,4) ,            
                        new SqlParameter("@OrderNo", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@OrderTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@OrderStatus", SqlDbType.Int,4) ,            
                        new SqlParameter("@OrderTitle", SqlDbType.NVarChar,255) ,            
                        new SqlParameter("@ShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ShopCustomerNumber", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@OldOrderPhone", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@CommodityQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@PayPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@DiscountPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@NormalPrice", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@RandomPayDiscount", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@CouponId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CouponValue", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@PayWay", SqlDbType.Int,4) ,            
                        new SqlParameter("@PaySerialId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ReceiverName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@ReceiverPhone", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@ReceiverAddress", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Remark", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@SysRemark", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@IsPayCompleted", SqlDbType.Bit,1) ,            
                        new SqlParameter("@ConsumerId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ConsumerName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@AssignerAccountId", SqlDbType.Int,4) ,            
                        new SqlParameter("@AssignTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@DeliveryAccountId", SqlDbType.Int,4) ,            
                        new SqlParameter("@DeliveryTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@ReceivingTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@FinishTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@CancelTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@RejectTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@RemindTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@ConsumerSendTime", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@CheckCode", SqlDbType.NVarChar,10) ,            
                        new SqlParameter("@IsPlatformOrder", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsEndingAfterPaid", SqlDbType.Bit,1) ,            
                        new SqlParameter("@ShareToken", SqlDbType.NVarChar,150) ,            
                        new SqlParameter("@Longitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@Latitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@IpAddress", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@OrderIdCopiedFrom", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsConvertFromOldVirtual", SqlDbType.Bit,1) ,            
                        new SqlParameter("@PriceDiffOfCopiedOrder", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@ConsumerOrderType", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsCommented", SqlDbType.Bit,1) ,            
                        new SqlParameter("@CanWithdraw", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsShopCanView", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.OrderNo;
            parameters[2].Value = model.OrderTime;
            parameters[3].Value = model.OrderStatus;
            parameters[4].Value = model.OrderTitle;
            parameters[5].Value = model.ShopId;
            parameters[6].Value = model.ShopCustomerNumber;
            parameters[7].Value = model.OldOrderPhone;
            parameters[8].Value = model.CommodityQuantity;
            parameters[9].Value = model.PayPrice;
            parameters[10].Value = model.DiscountPrice;
            parameters[11].Value = model.NormalPrice;
            parameters[12].Value = model.RandomPayDiscount;
            parameters[13].Value = model.CouponId;
            parameters[14].Value = model.CouponValue;
            parameters[15].Value = model.PayWay;
            parameters[16].Value = model.PaySerialId;
            parameters[17].Value = model.ReceiverName;
            parameters[18].Value = model.ReceiverPhone;
            parameters[19].Value = model.ReceiverAddress;
            parameters[20].Value = model.Remark;
            parameters[21].Value = model.SysRemark;
            parameters[22].Value = model.IsPayCompleted;
            parameters[23].Value = model.ConsumerId;
            parameters[24].Value = model.ConsumerName;
            parameters[25].Value = model.AssignerAccountId;
            parameters[26].Value = model.AssignTime;
            parameters[27].Value = model.DeliveryAccountId;
            parameters[28].Value = model.DeliveryTime;
            parameters[29].Value = model.ReceivingTime;
            parameters[30].Value = model.FinishTime;
            parameters[31].Value = model.CancelTime;
            parameters[32].Value = model.RejectTime;
            parameters[33].Value = model.RemindTime;
            parameters[34].Value = model.ConsumerSendTime;
            parameters[35].Value = model.CheckCode;
            parameters[36].Value = model.IsPlatformOrder;
            parameters[37].Value = model.IsEndingAfterPaid;
            parameters[38].Value = model.ShareToken;
            parameters[39].Value = model.Longitude;
            parameters[40].Value = model.Latitude;
            parameters[41].Value = model.IpAddress;
            parameters[42].Value = model.OrderIdCopiedFrom;
            parameters[43].Value = model.IsConvertFromOldVirtual;
            parameters[44].Value = model.PriceDiffOfCopiedOrder;
            parameters[45].Value = model.ConsumerOrderType;
            parameters[46].Value = model.IsCommented;
            parameters[47].Value = model.CanWithdraw;
            parameters[48].Value = model.IsShopCanView;
            parameters[49].Value = model.IsDelete; return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("update Orders set IsDelete=1 ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string Idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append("update Orders set IsDelete=1 ");
            strSql.Append(" where Id in (" + Idlist + ")  ");
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataSet GetModel(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, OrderNo, OrderTime, OrderStatus, OrderTitle, ShopId, ShopCustomerNumber, OldOrderPhone, CommodityQuantity, PayPrice, DiscountPrice, NormalPrice, RandomPayDiscount, CouponId, CouponValue, PayWay, PaySerialId, ReceiverName, ReceiverPhone, ReceiverAddress, Remark, SysRemark, IsPayCompleted, ConsumerId, ConsumerName, AssignerAccountId, AssignTime, DeliveryAccountId, DeliveryTime, ReceivingTime, FinishTime, CancelTime, RejectTime, RemindTime, ConsumerSendTime, CheckCode, IsPlatformOrder, IsEndingAfterPaid, ShareToken, Longitude, Latitude, IpAddress, OrderIdCopiedFrom, IsConvertFromOldVirtual, PriceDiffOfCopiedOrder, ConsumerOrderType, IsCommented, CanWithdraw, IsShopCanView, IsDelete  ");
            strSql.Append("  from Orders ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM Orders where IsDelete=0 ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion

        #region 扩展方法
        /// <summary>
        /// 得到一个订单信息主体
        /// </summary>
        public Orders GetOrderInfo(string orderNo)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * from Orders ");
            strSql.Append(" where OrderNo=@OrderNo  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@OrderNo", SqlDbType.NVarChar,50)
			};
            parameters[0].Value = orderNo;
            var lsRet = DbHelperSQL.ExecuteSqlList<Orders>(strSql.ToString(), parameters);
            return lsRet == null || lsRet.Count == 0 ? null : lsRet[0];
        }

        /// <summary>
        /// 得到一个订单信息主体
        /// </summary>
        public Orders GetOrderInfo(int id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * from Orders ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;
            return DbHelperSQL.ExecuteReader<Orders>(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获取订单分页数据
        /// </summary>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页数据条数</param>
        /// <param name="keyWord">关键字（收货人姓名、电话）</param>
        /// <param name="orderNo">订单号</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="shopIds">店铺Ids</param>
        /// <param name="orderStatus">订单状态</param>
        /// <param name="consumerId">用户ID</param>
        /// <param name="deliveryAccountId">配送人ID</param>
        /// <param name="colOrder">排序列（如单个：name desc   多个：name desc,age asc）</param>
        /// <param name="totalCount">条件查询总数</param>
        /// <param name="isShowPlatformOrder">是否显示平台订单</param>
        /// <returns>数据列表</returns>
        public List<Orders> GetPagingList(int pageIndex, int pageSize, ref int totalCount, string keyWord = null,
            string orderNo = null, string startTime = null, string endTime = null, string shopIds = null,
            int orderStatus = -1, int deliveryAccountId = 0, string colOrder = "Id desc", bool isShowPlatformOrder = false, int consumerId = 0, bool isMcUser = false)
        {
            var strWhere = new StringBuilder(" IsDelete=0 ");
            var parameters = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(keyWord))
            {
                strWhere.Append(" and ([ReceiverName] like @keyWord or [ReceiverPhone] like @keyWord)");
                parameters.Add(new SqlParameter { ParameterName = "@keyWord", Value = "%" + keyWord + "%" });
            }
            if (!string.IsNullOrEmpty(orderNo))
            {
                strWhere.Append(" and OrderNo=@OrderNo");
                parameters.Add(new SqlParameter { ParameterName = "@OrderNo", Value = orderNo});
            }
            if (!string.IsNullOrEmpty(startTime))
            {
                strWhere.Append(" and OrderTime>=@StartTime");
                parameters.Add(new SqlParameter { ParameterName = "@StartTime", Value = startTime });
            }
            if (!string.IsNullOrEmpty(endTime))
            {
                strWhere.Append(" and OrderTime<=@EndTime");
                endTime = DateTime.Parse(endTime).AddDays(1).ToString();
                parameters.Add(new SqlParameter { ParameterName = "@EndTime", Value = endTime });
            }
            if (!string.IsNullOrEmpty(shopIds))
            {
                strWhere.Append(" and ShopId in (" + shopIds.Trim(',') + ")");
            }
            if (orderStatus >= 0)
            {
                strWhere.Append(" and OrderStatus=" + orderStatus);
            }
            if (consumerId > 0)
            {
                strWhere.Append(" and ConsumerId=" + consumerId);
            }
            if (deliveryAccountId > 0)
            {
                strWhere.Append(" and DeliveryAccountId=" + deliveryAccountId);
            }
            if (!isShowPlatformOrder)
            {
                strWhere.Append(" and IsPlatformOrder=0 ");
            }
            if (isMcUser)
            {
                strWhere.Append(" and IsShopCanView=1");
            }
            return GetSimpleListPage<Orders>(pageIndex, pageSize, "Orders", null, null, colOrder, strWhere.ToString(), ref totalCount, parameters.ToArray());
        }

        #region 根据条件统计订单数量
        /// <summary>
        /// 根据条件统计订单数量
        /// </summary>
        /// <param name="ignoreDelete"></param>
        /// <param name="isGroupbyConsumerId"></param>
        /// <param name="isGroupbyShopId"></param>
        /// <param name="isGroupByOrderStatus"></param>
        /// <param name="isOrderByAmountDesc"></param>
        /// <param name="consumerId"></param>
        /// <param name="shopId"></param>
        /// <param name="orderStatus"></param>
        /// <returns></returns>
        public List<OrderStatDto> GetOrderAmountByStatus(bool ignoreDelete, bool isGroupbyConsumerId, bool isGroupbyShopId,
            bool isGroupByOrderStatus, bool isOrderByAmountDesc, int consumerId = 0, int shopId = 0, int orderStatus = -1)
        {
            var strSql = FormatOrderStatSql(ignoreDelete, isGroupbyConsumerId, isGroupbyShopId,
                isGroupByOrderStatus, isOrderByAmountDesc, consumerId, shopId, orderStatus);

            var lsOrderStatAmount = DbHelperSQL.ExecuteSqlList<OrderStatDto>(strSql);

            if (lsOrderStatAmount == null || lsOrderStatAmount.Count == 0)
            {
                return null;
            }
            return lsOrderStatAmount.ToList();
        }

        /// <summary>
        /// 为统计构建SQL语句
        /// </summary>
        /// <param name="ignoreDelete"></param>
        /// <param name="isGroupbyConsumerId"></param>
        /// <param name="isGroupbyShopId"></param>
        /// <param name="isGroupByOrderStatus"></param>
        /// <param name="isOrderByAmountDesc"></param>
        /// <param name="consumerId"></param>
        /// <param name="shopId"></param>
        /// <param name="orderStatus"></param>
        /// <returns></returns>
        private string FormatOrderStatSql(bool ignoreDelete, bool isGroupbyConsumerId, bool isGroupbyShopId,
            bool isGroupByOrderStatus, bool isOrderByAmountDesc, int consumerId = 0, int shopId = 0, int orderStatus = -1)
        {
            const string strSql = "SELECT COUNT(Id) AS {0} {1} FROM ORDERS {2} {3} {4}";
            var countColumnName = "OrderAmount";

            var strColumnGroupby = string.Empty;
            if (isGroupbyConsumerId)
            {
                strColumnGroupby += "ConsumerId";
            }
            if (isGroupbyShopId)
            {
                strColumnGroupby += ", ShopId";
            }
            if (isGroupByOrderStatus)
            {
                strColumnGroupby += ", OrderStatus";
            }
            var strGroupBy = string.Empty;
            if (!string.IsNullOrWhiteSpace(strColumnGroupby))
            {
                strGroupBy = "GROUP BY " + strColumnGroupby;
            }

            var strOrderBy = "ORDER BY " + countColumnName + (isOrderByAmountDesc ? " DESC" : " ASC");

            if (!string.IsNullOrWhiteSpace(strColumnGroupby))
            {
                countColumnName += ", ";
            }

            var strWhere = string.Empty;
            if (consumerId > 0)
            {
                strWhere += "ConsumerId=" + consumerId + " ";
            }
            if (shopId > 0)
            {
                strWhere += (string.IsNullOrWhiteSpace(strWhere) ? "" : " AND ") + "ShopId=" + shopId;
            }
            if (orderStatus > -1)
            {
                strWhere += (string.IsNullOrWhiteSpace(strWhere) ? "" : " AND ") + "OrderStatus=" +
                                     orderStatus;
            }
            if (ignoreDelete)
            {
                strWhere += (string.IsNullOrWhiteSpace(strWhere) ? "" : " AND ") + "IsDelete=0";
            }

            strWhere = string.IsNullOrWhiteSpace(strWhere) ? "" : ("WHERE " + strWhere);

            return string.Format(strSql, countColumnName, strColumnGroupby, strWhere, strGroupBy,
                strOrderBy);
        }
        #endregion

        /// <summary>
        /// 获取指定时间内立减金额总数
        /// </summary>
        /// <param name="beginTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public decimal GetTotalPayDiscountAmount(DateTime beginTime, DateTime endTime)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT SUM(RandomPayDiscount)");
            sbSql.Append("FROM Orders ");
            sbSql.Append("WHERE OrderTime>=@BeginTime AND OrderTime<@EndTime AND IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@BeginTime", SqlDbType.DateTime),
                    new SqlParameter("@EndTime", SqlDbType.DateTime)
			};
            parameters[0].Value = beginTime;
            parameters[1].Value = endTime;

            var obj = DbHelperSQL.GetSingle(sbSql.ToString(), parameters);
            return obj == null ? 0 : Convert.ToDecimal(obj);
        }

        /// <summary>
        /// 更新订单状态
        /// </summary>
        public bool UpdateOrderStatus(Orders model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update Orders set ");
            strSql.Append(" OrderStatus = @OrderStatus ");
            strSql.Append(" where Id=@Id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@Id", SqlDbType.Int,4) ,          
                        new SqlParameter("@OrderStatus", SqlDbType.Int,4)           
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.OrderStatus;
            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 根据条件获取订单总数
        /// </summary>
        /// <remarks>2016-08-22 万乐园 创建</remarks>
        public int GetTotalCount(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select count(1) FROM Orders where IsDelete=0");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return int.Parse(DbHelperSQL.GetSingle(strSql.ToString()).ToString());
        }

        /// <summary>
        /// 获得导出订单数据列表
        /// </summary>
        /// <remarks>2016-08-26 万乐园 创建</remarks>
        public DataSet GetExportOrderList(string keyWord, string orderNo, string startTime, string endTime, string shopIds, int orderStatus)
        {
            var strSql = new StringBuilder("select * FROM Orders where IsDelete=0");
            var parameters = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(keyWord))
            {
                strSql.Append(" and ([ReceiverName] like @keyWord or [ReceiverPhone] like @keyWord)");
                parameters.Add(new SqlParameter { ParameterName = "@keyWord", Value = "%" + keyWord + "%" });
            }
            if (!string.IsNullOrEmpty(orderNo))
            {
                strSql.Append(" and OrderNo=@OrderNo");
                parameters.Add(new SqlParameter { ParameterName = "@OrderNo", Value = orderNo });
            }
            if (!string.IsNullOrEmpty(startTime))
            {
                strSql.Append(" and OrderTime>=@StartTime");
                parameters.Add(new SqlParameter { ParameterName = "@StartTime", Value = startTime });
            }
            if (!string.IsNullOrEmpty(endTime))
            {
                strSql.Append(" and OrderTime<=@EndTime");
                endTime = DateTime.Parse(endTime).AddDays(1).ToString();
                parameters.Add(new SqlParameter { ParameterName = "@EndTime", Value = endTime });
            }
            if (!string.IsNullOrEmpty(shopIds))
            {
                strSql.Append(" and ShopId in (" + shopIds.Trim(',') + ")");
            }
            if (orderStatus >= 0)
            {
                strSql.Append(" and OrderStatus=" + orderStatus);
            }
            strSql.Append(" order by id asc");
            return DbHelperSQL.Query(strSql.ToString(), parameters.ToArray());
        }
        
        /// <summary>
        /// 获取用户可操作订单列表
        /// </summary>
        /// <remarks>2016-09-06 万乐园 创建</remarks>
        public DataSet GetOperableOrderList(int consumerId)
        {
            var strSql = new StringBuilder("select");
            strSql.Append(" os.Id AS OrderId, os.OrderNo, os.OrderTime, os.OrderStatus, os.OrderTitle, os.ShopId, os.ShopCustomerNumber, os.OldOrderPhone, ");
            strSql.Append("os.CommodityQuantity, os.PayPrice, os.DiscountPrice, os.NormalPrice, os.RandomPayDiscount, os.CouponId, ");
            strSql.Append("os.CouponValue, os.PayWay, os.PaySerialId, os.ReceiverName, os.ReceiverPhone, os.ReceiverAddress, os.Remark, ");
            strSql.Append("os.IsPayCompleted, os.ConsumerId, os.ConsumerName, os.AssignerAccountId, os.AssignTime, os.DeliveryAccountId, ");
            strSql.Append("os.DeliveryTime, os.ReceivingTime, os.FinishTime, os.CancelTime, os.RejectTime, os.RemindTime, ");
            strSql.Append("os.ConsumerSendTime, os.CheckCode, os.ShareToken, os.Longitude, os.Latitude, os.IpAddress, ");
            strSql.Append("os.OrderIdCopiedFrom, os.IsEndingAfterPaid, os.IsConvertFromOldVirtual, os.PriceDiffOfCopiedOrder, os.ConsumerOrderType, os.IsCommented, os.CanWithdraw, os.IsShopCanView, os.IsDelete,  ");
            strSql.Append("ss.[Name] AS ShopName ");
            strSql.Append(" from Orders os");
            strSql.Append(" left join");
            strSql.Append(" Shops ss on os.ShopId=ss.Id");
            strSql.Append(" where os.IsDelete=0 and os.ConsumerId=@ConsumerId");
            strSql.Append(" and (os.OrderStatus in (2,3) or (os.OrderStatus=4 and os.IsCommented=0))");
            SqlParameter[] parameters = {       
                new SqlParameter("@ConsumerId", SqlDbType.Int,4)
			};
            parameters[0].Value = consumerId;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }
        #endregion
    }
}
